using System;
using System.Data;
using System.Data.OleDb;
using PCSComUtils.Common;
using PCSComUtils.DataAccess;

using PCSComUtils.PCSExc;

namespace PCSComPayroll.DS
{
	public class PAY_EstimateDetailDS
	{
		public PAY_EstimateDetailDS()
		{
		}

		private const string THIS = "PCSComPayroll.DS.DS.PAY_EstimateDetailDS";
		public void Add(object pobjObjectVO)
		{
			const string METHOD_NAME = THIS + ".Add()";

			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				PAY_EstimateDetailVO objObject = (PAY_EstimateDetailVO) pobjObjectVO;
				string strSql = String.Empty;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand("", oconPCS);

				strSql = "INSERT INTO PAY_EstimateDetail("
					+ PAY_EstimateDetailTable.ESTIMATEMASTERID_FLD + ","
					+ PAY_EstimateDetailTable.PRODUCTIONLINEID_FLD + ","
					+ PAY_EstimateDetailTable.TOTALTIME_FLD + ","
					+ PAY_EstimateDetailTable.AMOUNT_FLD + ","
					+ PAY_EstimateDetailTable.AVAILABLEAMOUNT_FLD + ","
					+ PAY_EstimateDetailTable.REQUESTAMOUNT_FLD + ","
					+ PAY_EstimateDetailTable.USERID_FLD + ")"
					+ "VALUES(?,?,?,?,?,?,?)";

				ocmdPCS.Parameters.Add(new OleDbParameter(PAY_EstimateDetailTable.ESTIMATEMASTERID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PAY_EstimateDetailTable.ESTIMATEMASTERID_FLD].Value = objObject.EstimateMasterID;

				ocmdPCS.Parameters.Add(new OleDbParameter(PAY_EstimateDetailTable.PRODUCTIONLINEID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PAY_EstimateDetailTable.PRODUCTIONLINEID_FLD].Value = objObject.ProductionLineID;

				ocmdPCS.Parameters.Add(new OleDbParameter(PAY_EstimateDetailTable.TOTALTIME_FLD, OleDbType.Decimal));
				ocmdPCS.Parameters[PAY_EstimateDetailTable.TOTALTIME_FLD].Value = objObject.TotalTime;

				ocmdPCS.Parameters.Add(new OleDbParameter(PAY_EstimateDetailTable.AMOUNT_FLD, OleDbType.Decimal));
				ocmdPCS.Parameters[PAY_EstimateDetailTable.AMOUNT_FLD].Value = objObject.Amount;

				ocmdPCS.Parameters.Add(new OleDbParameter(PAY_EstimateDetailTable.AVAILABLEAMOUNT_FLD, OleDbType.Decimal));
				ocmdPCS.Parameters[PAY_EstimateDetailTable.AVAILABLEAMOUNT_FLD].Value = objObject.AvailableAmount;

				ocmdPCS.Parameters.Add(new OleDbParameter(PAY_EstimateDetailTable.REQUESTAMOUNT_FLD, OleDbType.Decimal));
				ocmdPCS.Parameters[PAY_EstimateDetailTable.REQUESTAMOUNT_FLD].Value = objObject.RequestAmount;

				ocmdPCS.Parameters.Add(new OleDbParameter(PAY_EstimateDetailTable.USERID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PAY_EstimateDetailTable.USERID_FLD].Value = objObject.UserID;


				ocmdPCS.CommandText = strSql;
				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();

			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}

			catch (InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}

		public void Delete(int pintID)
		{
			const string METHOD_NAME = THIS + ".Delete()";
			string strSql = String.Empty;
			strSql = "DELETE " + PAY_EstimateDetailTable.TABLE_NAME + " WHERE  " + "EstimateDetailID" + "=" + pintID.ToString();
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();
				ocmdPCS = null;

			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}

			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}


			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}

		public object GetObjectVO(int pintID)
		{
			const string METHOD_NAME = THIS + ".GetObjectVO()";
			OleDbDataReader odrPCS = null;
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = String.Empty;
				strSql = "SELECT "
					+ PAY_EstimateDetailTable.ESTIMATEDETAILID_FLD + ","
					+ PAY_EstimateDetailTable.ESTIMATEMASTERID_FLD + ","
					+ PAY_EstimateDetailTable.PRODUCTIONLINEID_FLD + ","
					+ PAY_EstimateDetailTable.TOTALTIME_FLD + ","
					+ PAY_EstimateDetailTable.AMOUNT_FLD + ","
					+ PAY_EstimateDetailTable.AVAILABLEAMOUNT_FLD + ","
					+ PAY_EstimateDetailTable.REQUESTAMOUNT_FLD + ","
					+ PAY_EstimateDetailTable.USERID_FLD
					+ " FROM " + PAY_EstimateDetailTable.TABLE_NAME
					+ " WHERE " + PAY_EstimateDetailTable.ESTIMATEDETAILID_FLD + "=" + pintID;

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Connection.Open();
				odrPCS = ocmdPCS.ExecuteReader();

				PAY_EstimateDetailVO objObject = new PAY_EstimateDetailVO();

				while (odrPCS.Read())
				{
					objObject.EstimateDetailID = int.Parse(odrPCS[PAY_EstimateDetailTable.ESTIMATEDETAILID_FLD].ToString());
					objObject.EstimateMasterID = int.Parse(odrPCS[PAY_EstimateDetailTable.ESTIMATEMASTERID_FLD].ToString());
					objObject.ProductionLineID = int.Parse(odrPCS[PAY_EstimateDetailTable.PRODUCTIONLINEID_FLD].ToString());
					objObject.TotalTime = Decimal.Parse(odrPCS[PAY_EstimateDetailTable.TOTALTIME_FLD].ToString());
					objObject.Amount = Decimal.Parse(odrPCS[PAY_EstimateDetailTable.AMOUNT_FLD].ToString());
					objObject.AvailableAmount = Decimal.Parse(odrPCS[PAY_EstimateDetailTable.AVAILABLEAMOUNT_FLD].ToString());
					objObject.RequestAmount = Decimal.Parse(odrPCS[PAY_EstimateDetailTable.REQUESTAMOUNT_FLD].ToString());
					objObject.UserID = int.Parse(odrPCS[PAY_EstimateDetailTable.USERID_FLD].ToString());

				}
				return objObject;
			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}

			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}

		public void Update(object pobjObjecVO)
		{
			const string METHOD_NAME = THIS + ".Update()";

			PAY_EstimateDetailVO objObject = (PAY_EstimateDetailVO) pobjObjecVO;


			//prepare value for parameters
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = String.Empty;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				strSql = "UPDATE PAY_EstimateDetail SET "
					+ PAY_EstimateDetailTable.ESTIMATEMASTERID_FLD + "=   ?" + ","
					+ PAY_EstimateDetailTable.PRODUCTIONLINEID_FLD + "=   ?" + ","
					+ PAY_EstimateDetailTable.TOTALTIME_FLD + "=   ?" + ","
					+ PAY_EstimateDetailTable.AMOUNT_FLD + "=   ?" + ","
					+ PAY_EstimateDetailTable.AVAILABLEAMOUNT_FLD + "=   ?" + ","
					+ PAY_EstimateDetailTable.REQUESTAMOUNT_FLD + "=   ?" + ","
					+ PAY_EstimateDetailTable.USERID_FLD + "=  ?"
					+ " WHERE " + PAY_EstimateDetailTable.ESTIMATEDETAILID_FLD + "= ?";

				ocmdPCS.Parameters.Add(new OleDbParameter(PAY_EstimateDetailTable.ESTIMATEMASTERID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PAY_EstimateDetailTable.ESTIMATEMASTERID_FLD].Value = objObject.EstimateMasterID;

				ocmdPCS.Parameters.Add(new OleDbParameter(PAY_EstimateDetailTable.PRODUCTIONLINEID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PAY_EstimateDetailTable.PRODUCTIONLINEID_FLD].Value = objObject.ProductionLineID;

				ocmdPCS.Parameters.Add(new OleDbParameter(PAY_EstimateDetailTable.TOTALTIME_FLD, OleDbType.Decimal));
				ocmdPCS.Parameters[PAY_EstimateDetailTable.TOTALTIME_FLD].Value = objObject.TotalTime;

				ocmdPCS.Parameters.Add(new OleDbParameter(PAY_EstimateDetailTable.AMOUNT_FLD, OleDbType.Decimal));
				ocmdPCS.Parameters[PAY_EstimateDetailTable.AMOUNT_FLD].Value = objObject.Amount;

				ocmdPCS.Parameters.Add(new OleDbParameter(PAY_EstimateDetailTable.AVAILABLEAMOUNT_FLD, OleDbType.Decimal));
				ocmdPCS.Parameters[PAY_EstimateDetailTable.AVAILABLEAMOUNT_FLD].Value = objObject.AvailableAmount;

				ocmdPCS.Parameters.Add(new OleDbParameter(PAY_EstimateDetailTable.REQUESTAMOUNT_FLD, OleDbType.Decimal));
				ocmdPCS.Parameters[PAY_EstimateDetailTable.REQUESTAMOUNT_FLD].Value = objObject.RequestAmount;

				ocmdPCS.Parameters.Add(new OleDbParameter(PAY_EstimateDetailTable.USERID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PAY_EstimateDetailTable.USERID_FLD].Value = objObject.UserID;

				ocmdPCS.Parameters.Add(new OleDbParameter(PAY_EstimateDetailTable.ESTIMATEDETAILID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PAY_EstimateDetailTable.ESTIMATEDETAILID_FLD].Value = objObject.EstimateDetailID;


				ocmdPCS.CommandText = strSql;
				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();
			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}

			catch (InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}

		}

		public DataSet List()
		{
			const string METHOD_NAME = THIS + ".List()";
			DataSet dstPCS = new DataSet();


			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = String.Empty;

				strSql = "SELECT "
					+ PAY_EstimateDetailTable.ESTIMATEDETAILID_FLD + ","
					+ PAY_EstimateDetailTable.ESTIMATEMASTERID_FLD + ","
					+ PAY_EstimateDetailTable.PRODUCTIONLINEID_FLD + ","
					+ PAY_EstimateDetailTable.TOTALTIME_FLD + ","
					+ PAY_EstimateDetailTable.AMOUNT_FLD + ","
					+ PAY_EstimateDetailTable.AVAILABLEAMOUNT_FLD + ","
					+ PAY_EstimateDetailTable.REQUESTAMOUNT_FLD + ","
					+ PAY_EstimateDetailTable.USERID_FLD
					+ " FROM " + PAY_EstimateDetailTable.TABLE_NAME;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();

				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dstPCS, PAY_EstimateDetailTable.TABLE_NAME);

				return dstPCS;
			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}


		}

		public DataSet List(int pintMasterID)
		{
			const string METHOD_NAME = THIS + ".List()";
			DataSet dstPCS = new DataSet();


			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = "SELECT D.EstimateDetailID, D.EstimateMasterID, D.ProductionLineID,"
					+ " P.Code AS PRO_ProductionLineCode,"
					+ " TotalTime, Amount, ApprovalAmount, AvailableAmount,"
					+ " RequestLimit, AdvanceLimit, RequestAmount, ApprovedRequestAmount,"
					+ " AdvanceAmount, ApprovedAdvanceAmount"
					+ " FROM PAY_EstimateDetail D JOIN PAY_EstimateMaster M"
					+ " ON D.EstimateMasterID = M.EstimateMasterID"
					+ " JOIN PRO_ProductionLine P ON D.ProductionLineID = P.ProductionLineID"
					+ " WHERE M.EstimateMasterID = " + pintMasterID;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();

				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dstPCS, PAY_EstimateDetailTable.TABLE_NAME);

				return dstPCS;
			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}
			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}

		public DataSet ListBySale(int pintSaleOrderDetailID)
		{
			const string METHOD_NAME = THIS + ".ListBySale()";
			DataSet dstPCS = new DataSet();
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = "SELECT 0 EstimateDetailID, 0 EstimateMasterID,"
					+ " PAY_ProductionNorm.ProductionLineID, P.Code PRO_ProductionLineCode,"
					+ " SUM(ISNULL(TotalTime,0)) TotalTime,"
					+ " ROUND(SUM(ISNULL(TotalTime,0)) / 60 * ISNULL(("
					+ " SELECT TOP 1 ISNULL(PaidPerHour,0) FROM PAY_PayrollParam"
					+ " WHERE ISNULL(Status,0) = 1"
					+ " ),0),-3) Amount, 0.00 ApprovalAmount,"
					+ " ROUND(SUM(ISNULL(TotalTime,0)) / 60 * ISNULL(("
					+ " SELECT TOP 1 ISNULL(PaidPerHour,0) FROM PAY_PayrollParam"
					+ " WHERE ISNULL(Status,0) = 1"
					+ " ),0),-3) AvailableAmount,"
					+ " NULL RequestLimit, NULL AdvanceLimit, 0.00 RequestAmount,"
					+ " 0.00 ApprovedRequestAmount, 0.00 AdvanceAmount, 0.00 ApprovedAdvanceAmount"
					+ " FROM PAY_ProductionNorm JOIN PRO_ProductionLine P"
					+ " ON PAY_ProductionNorm.ProductionLineID = P.ProductionLineID"
					+ " JOIN SO_SaleOrderDetail S ON PAY_ProductionNorm.SaleOrderDetailID = S.SaleOrderDetailID"
					+ " WHERE S.SaleOrderDetailID = " + pintSaleOrderDetailID
					+ " GROUP BY PAY_ProductionNorm.ProductionLineID, P.Code, S.OrderQuantity";
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();

				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dstPCS, PAY_EstimateDetailTable.TABLE_NAME);

				return dstPCS;
			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}

		public void UpdateDataSet(DataSet pData)
		{
			const string METHOD_NAME = THIS + ".UpdateDataSet()";
			string strSql;
			OleDbConnection oconPCS = null;
			OleDbCommandBuilder odcbPCS;
			OleDbDataAdapter odadPCS = new OleDbDataAdapter();

			try
			{
				strSql = "SELECT "
					+ PAY_EstimateDetailTable.ESTIMATEDETAILID_FLD + ","
					+ PAY_EstimateDetailTable.ESTIMATEMASTERID_FLD + ","
					+ PAY_EstimateDetailTable.PRODUCTIONLINEID_FLD + ","
					+ PAY_EstimateDetailTable.TOTALTIME_FLD + ","
					+ PAY_EstimateDetailTable.AMOUNT_FLD + ","
					+ PAY_EstimateDetailTable.APPROVALAMOUNT_FLD + ","
					+ PAY_EstimateDetailTable.AVAILABLEAMOUNT_FLD + ","
					+ PAY_EstimateDetailTable.REQUESTAMOUNT_FLD + ","
					+ PAY_EstimateDetailTable.REQUESTLIMIT_FLD + ","
					+ PAY_EstimateDetailTable.ADVANCELIMIT_FLD + ","
					+ PAY_EstimateDetailTable.ADVANCEAMOUNT_FLD + ","
					+ PAY_EstimateDetailTable.APPROVEDADVANCEAMOUNT_FLD + ","
					+ PAY_EstimateDetailTable.APPROVEDREQUESTAMOUNT_FLD
					+ " FROM " + PAY_EstimateDetailTable.TABLE_NAME;


				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				odadPCS.SelectCommand = new OleDbCommand(strSql, oconPCS);
				odcbPCS = new OleDbCommandBuilder(odadPCS);
				pData.EnforceConstraints = false;
				odadPCS.Update(pData, PAY_EstimateDetailTable.TABLE_NAME);

			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}

			catch (InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}

		}

		public decimal GetAvailableAmount(int pintSaleOrderDetailID, int pintProductionLineId)
		{
			const string METHOD_NAME = THIS + ".GetAvailableAmount()";
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = "SELECT SUM(Amount) AS AvailableAmount"
					+ " FROM PAY_EstimateDetail D JOIN PAY_EstimateMaster M"
					+ " ON D.EstimateMasterID = M.EstimateMasterID"
					+ " WHERE D.ProductionLineID = " + pintProductionLineId
					+ " AND M.SaleOrderDetailID = " + pintSaleOrderDetailID;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();

				object objResult = ocmdPCS.ExecuteScalar();
				try
				{
					return Convert.ToDecimal(objResult);
				}
				catch
				{
					return 0;
				}
			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}

		public decimal GetCompleteAmount(int pintSaleOrderDetailID, int pintProductionLineId)
		{
			const string METHOD_NAME = THIS + ".GetCompleteAmount()";
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = "SELECT SUM(CompletedQuantity * ISNULL(LaborRunTime,0)) * ISNULL(("
					+ " SELECT TOP 1 ISNULL(PaidPerHour,0) FROM PAY_PayrollParam"
					+ " WHERE ISNULL(Status,0) = 1 ),0) / 60 AS Amount"
					+ " FROM PRO_WorkOrderCompletion C JOIN PRO_WorkOrderMaster M"
					+ " ON C.WorkOrderMasterID = M.WorkOrderMasterID"
					+ " JOIN PRO_WorkOrderDetail D ON C.WorkOrderDetailID = D.WorkOrderDetailID"
					+ " JOIN PRO_WORouting R ON R.WorkOrderDetailID = C.WorkOrderDetailID"
					+ " WHERE QAStatus = '" +(int)QAStatusEnum.Passed + "'"
					+ " AND D.ProductionLineID = " + pintProductionLineId
					+ " AND D.SaleOrderDetailID = " + pintSaleOrderDetailID;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();

				object objResult = ocmdPCS.ExecuteScalar();
				try
				{
					return Convert.ToDecimal(objResult);
				}
				catch
				{
					return 0;
				}
			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}

		public decimal GetTotalRequest(int pintSaleOrderDetailID, int pintProductionLineId)
		{
			const string METHOD_NAME = THIS + ".GetTotalRequest()";
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = "SELECT SUM(RequestAmount)"
					+ " FROM PAY_PaymentRequestDetail D JOIN PAY_PaymentRequestMaster M"
					+ " ON D.PaymentRequestMasterID = M.PaymentRequestMasterID"
					+ " WHERE M.ProductionLineID = " + pintProductionLineId
					+ " AND M.SaleOrderDetailID = " + pintSaleOrderDetailID;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();

				object objResult = ocmdPCS.ExecuteScalar();
				try
				{
					return Convert.ToDecimal(objResult);
				}
				catch
				{
					return 0;
				}
			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}

		public decimal GetTotalPaymentRequest(int pintSaleOrderDetailID, int pintProductionLineId)
		{
			const string METHOD_NAME = THIS + ".GetTotalPaymentRequest()";
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = "SELECT SUM(RequestAmount)"
					+ " FROM PAY_PaymentRequestDetail D JOIN PAY_PaymentRequestMaster M"
					+ " ON D.PaymentRequestMasterID = M.PaymentRequestMasterID"
					+ " WHERE M.ProductionLineID = " + pintProductionLineId
					+ " AND M.SaleOrderDetailID = " + pintSaleOrderDetailID
					+ " AND RequestType = " + (int)RequestType.Payment;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();

				object objResult = ocmdPCS.ExecuteScalar();
				try
				{
					return Convert.ToDecimal(objResult);
				}
				catch
				{
					return 0;
				}
			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}

		public bool IsWOCompleted(int pintSaleOrderDetailID, int pintProductionLineID)
		{
			const string METHOD_NAME = THIS + ".IsWOCompleted()";
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = "SELECT COUNT(*) FROM PRO_WorkOrderDetail"
					+ " WHERE SaleOrderDetailID = " + pintSaleOrderDetailID
					+ " AND ProductionLineID = " + pintProductionLineID
					+ " AND Status IN (" + (int)WOLineStatus.FinClose + "," + (int)WOLineStatus.MfgClose + ")";
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();
				object objResult = ocmdPCS.ExecuteScalar();
				int intResult = 0;
				try
				{
					intResult = Convert.ToInt32(objResult);
				}
				catch{}
				return intResult > 0;
			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}
		public void UpdateAmount(int pintEstimasteMasterID, int pintProductionLineID, int pintSaleOrderDetailID)
		{
			const string METHOD_NAME = THIS + ".UpdateAmount()";
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = "UPDATE PAY_EstimateDetail"
					+ "\n SET RequestAmount = ISNULL((SELECT SUM(RequestAmount)"
					+ "\n FROM PAY_PaymentRequestDetail D JOIN PAY_PaymentRequestMaster M"
					+ "\n ON D.PaymentRequestMasterID = M.PaymentRequestMasterID"
					+ "\n AND M.ProductionLineID = " + pintProductionLineID
					+ "\n AND M.SaleOrderDetailID = " + pintSaleOrderDetailID
					+ "\n AND RequestType = 0),0),"
					+ "\n ApprovedRequestAmount = ISNULL((SELECT SUM(ApprovalAmount)"
					+ "\n FROM PAY_PaymentRequestDetail D JOIN PAY_PaymentRequestMaster M"
					+ "\n ON D.PaymentRequestMasterID = M.PaymentRequestMasterID"
					+ "\n AND M.ProductionLineID = " + pintProductionLineID
					+ "\n AND M.SaleOrderDetailID = " + pintSaleOrderDetailID
					+ "\n AND RequestType = 0),0),"
					+ "\n AdvanceAmount = ISNULL((SELECT SUM(RequestAmount)"
					+ "\n FROM PAY_PaymentRequestDetail D JOIN PAY_PaymentRequestMaster M"
					+ "\n ON D.PaymentRequestMasterID = M.PaymentRequestMasterID"
					+ "\n AND M.ProductionLineID = " + pintProductionLineID
					+ "\n AND M.SaleOrderDetailID = " + pintSaleOrderDetailID
					+ "\n AND RequestType = 1),0),"
					+ "\n ApprovedAdvanceAmount = ISNULL((SELECT SUM(ApprovalAmount)"
					+ "\n FROM PAY_PaymentRequestDetail D JOIN PAY_PaymentRequestMaster M"
					+ "\n ON D.PaymentRequestMasterID = M.PaymentRequestMasterID"
					+ "\n AND M.ProductionLineID = " + pintProductionLineID
					+ "\n AND M.SaleOrderDetailID = " + pintSaleOrderDetailID
					+ "\n AND RequestType = 1),0),"
					+ "\n ApprovalAmount = ISNULL((SELECT SUM(ApprovalAmount)"
					+ "\n FROM PAY_PaymentRequestDetail D JOIN PAY_PaymentRequestMaster M"
					+ "\n ON D.PaymentRequestMasterID = M.PaymentRequestMasterID"
					+ "\n AND M.ProductionLineID = " + pintProductionLineID
					+ "\n AND M.SaleOrderDetailID = " + pintSaleOrderDetailID + "),0),"
					+ "\n AvailableAmount = Amount - ISNULL((SELECT SUM(RequestAmount)"
					+ "\n FROM PAY_PaymentRequestDetail D JOIN PAY_PaymentRequestMaster M"
					+ "\n ON D.PaymentRequestMasterID = M.PaymentRequestMasterID"
					+ "\n AND M.ProductionLineID = " + pintProductionLineID
					+ "\n AND M.SaleOrderDetailID = " + pintSaleOrderDetailID + "),0)"
					+ "\n WHERE ProductionLineID =" + pintProductionLineID
					+ "\n AND " + PAY_EstimateDetailTable.ESTIMATEMASTERID_FLD + " = " + pintEstimasteMasterID;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();
				ocmdPCS = null;
			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}

			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}


			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}

		public DataTable GetEstimateDetailBySale(int pintSaleOrderDetailID)
		{
			const string METHOD_NAME = THIS + ".GetEstimateDetailBySale()";
			DataSet dstPCS = new DataSet();
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = "SELECT M.Completed, D.* FROM " + PAY_EstimateDetailTable.TABLE_NAME + " D"
					+ " JOIN " + PAY_EstimateMasterTable.TABLE_NAME + " M"
					+ " ON D." + PAY_EstimateDetailTable.ESTIMATEMASTERID_FLD + " = M." + PAY_EstimateMasterTable.ESTIMATEMASTERID_FLD
					+ " WHERE " + PAY_EstimateMasterTable.SALEORDERDETAILID_FLD + "=" + pintSaleOrderDetailID;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();

				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dstPCS, PAY_EstimateDetailTable.TABLE_NAME);

				return dstPCS.Tables[0];
			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}
	}
}